package cn.lili.modules.system.mapper;

import cn.lili.common.vo.PageVO;
import cn.lili.modules.goods.entity.dto.GoodsAndStores;
import cn.lili.modules.goods.entity.dto.GoodsSearchParams;
import cn.lili.modules.goods.entity.vos.GoodsVO;
import cn.lili.modules.member.entity.dos.Member;
import cn.lili.modules.member.entity.vo.GoodsCollectionVO;
import cn.lili.modules.member.entity.vo.MemberVO;
import cn.lili.modules.order.order.entity.dos.Order;
import cn.lili.modules.permission.entity.dos.Role;
import cn.lili.modules.store.entity.dos.Store;
import cn.lili.modules.store.entity.vos.StoreVO;
import cn.lili.modules.system.entity.dos.AppVersion;
import cn.lili.modules.system.entity.dos.Region;
import cn.lili.modules.wallet.entity.dto.CommissionDistributionSale;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 行政地区数据处理层
 *
 * @author Chopper
 * @since 2020/12/8 9:46
 */
@Mapper
public interface RegionMapper extends BaseMapper<Region> {

	/**
	 * 根据id查询区域Cod
	 * 
	 * @return
	 */
	@Select("SELECT DISTINCT  ad_code adCode FROM li_region WHERE id = #{id} or find_in_set( #{id} , path )  ")
	List<Map<String, Object>> getItemAdCod(String id);

	/**
	 * 根据id查询区域Cod
	 * 
	 * @return
	 */
	@Select("SELECT DISTINCT  ad_code AS adCode FROM li_region WHERE ad_code like '${addCode}' ")
	List<Map<String, Object>> getItemAdCodByAdcode(@Param("addCode") String addCode);

	/**
	 * 根据code查询合伙人管理的区域Code
	 *
	 * @return
	 */
	@Select("SELECT DISTINCT  ad_code AS adCode FROM li_region WHERE ad_code like '${addCode}'"
			+ " and ad_code not in ( SELECT DISTINCT region_id from "
			+ " li_partner where partner_type = '${type}' and partner_state = 0 "
			+ " and delete_flag = 0 and audit_state = 'PASS' and region_id != '${adCode}')")
	List<Map<String, Object>> getPartnerAdCodeByAdcode(@Param("addCode") String addCode, @Param("type") String type,
			@Param("adCode") String adCode);

	/**
	 * 根据adCode查询上级省市区
	 * 
	 * @return
	 */
	@Select("SELECT *  FROM li_region WHERE  level <> 'street' and ad_code = #{adCode} ")
	Region getItemAdCodAll(String adCode);

	/**
	 * 根据区域查询区域的id
	 */
	@Select(" select * from li_region  ${ew.customSqlSegment} ")
	Region getRegionId(@Param(Constants.WRAPPER) Wrapper<Region> queryWrapper);

	/**
	 * 查询商品VO分页
	 *
	 * @param page
	 *            分页
	 * @param queryWrapper
	 *            查询条件
	 * @return 商品VO分页
	 */
	@Select("select g.* from li_goods g left join li_store a  on a.id = g.store_id  ${ew.customSqlSegment}")
	IPage<GoodsVO> queryByParams(IPage<GoodsSearchParams> page,
			@Param(Constants.WRAPPER) Wrapper<GoodsSearchParams> queryWrapper);

	/**
	 * 本区域销量前三的商品
	 *
	 * @return 商品VO分页
	 */
	@Select("select g.* ,count(*) aa from li_store a  left join li_order o on a.id =  o.store_id "
			+ " left join li_order_item oo on o.sn=oo.order_sn"
			+ " left join li_goods g on g.id = oo.goods_id where a.store_address_id_path like concat('%', #{storeAddressIdPath}, '%') AND oo.id is not null GROUP BY oo.goods_id  ORDER BY aa desc limit #{sunm}")
	List<GoodsVO> getCommodity(String storeAddressIdPath, int sunm);

	/**
	 * 全国
	 *
	 * @return 商品VO分页
	 */
	@Select("select g.* ,count(*) aa from li_store a  left join li_order o on a.id =  o.store_id "
			+ " left join li_order_item oo on o.sn=oo.order_sn"
			+ " left join li_goods g on g.id = oo.goods_id where oo.id is not null GROUP BY oo.goods_id  ORDER BY aa desc limit #{sunm}")
	List<GoodsVO> getCommodityQg(int sunm);

	/**
	 * 根据区域查询区域的id
	 */
	@Select(" select * from li_region where level in('district','city','province') and ad_code = #{adCode}  ")
	Region getAdRegionId(String adCode);

	/**
	 * 区域前14店铺
	 */
	@Select("select a.*,COUNT(0) CC from li_store a  left join li_order o on a.id =  o.store_id  where a.store_address_id_path like concat('%', #{storeAddressIdPath}, '%') and a.id is not null and  store_disable='OPEN'    GROUP BY a.id  ORDER BY CC desc limit #{sunm}")
	List<Store> getCommodityShop(String storeAddressIdPath, int sunm);

	/**
	 * 全国前14店铺
	 */
	@Select("select a.*,COUNT(0) CC from li_store a  left join li_order o on a.id =  o.store_id  where a.id is not null and  store_disable='OPEN'   GROUP BY a.id  ORDER BY CC desc limit #{sunm}")
	List<Store> getCommodityShopQg(int sunm);

	/**
	 * 获取店铺分页列表
	 *
	 * @param page
	 *            分页
	 * @param queryWrapper
	 *            查询条件
	 * @return 店铺VO分页列表
	 */
	@Select("SELECT * FROM (select a.*,IFNULL(Floor(6371000 * acos (cos ( radians(#{lat}) ) * cos( radians(a.lat) ) * cos( radians(a.lon) - radians(#{lon}) ) +"
			+ " sin ( radians(#{lat}) ) * sin( radians(a.lat) ) ) ),0)AS distance , IFNULL((select count(0) "
			+ " FROM li_order o "
			+ " WHERE o.store_id = a.id and o.order_status='COMPLETED' ),0) as monthSales, FORMAT(IFNULL((select SUM(e.store_score)/COUNT(e.store_score) "
			+ " FROM li_store_evaluation e  WHERE e.store_id = a.id),5),1) as score from li_store a) as s  ${ew.customSqlSegment}")
	IPage<StoreVO> getStoreList(IPage<StoreVO> page, @Param(Constants.WRAPPER) Wrapper<StoreVO> queryWrapper,
			@Param("lon") String lon, @Param("lat") String lat);

	@Select("<script> "
			+ "SELECT * FROM (select *,IFNULL(Floor(6371000 * acos (cos ( radians(#{lat}) ) * cos( radians(lat) ) * cos( radians(lon) - radians(#{lon}) ) "
			+ "   + sin ( radians(#{lat}) ) * sin( radians(lat) ) ) ),0)AS distance , "
			+ "IFNULL((select count(0) FROM li_order o WHERE o.store_id = s.id and o.order_status='COMPLETED' ),0) as monthSales, "
			+ "FORMAT(IFNULL((select SUM(e.store_score)/COUNT(e.store_score) FROM li_store_evaluation e WHERE e.store_id = s.id),5),1) as score "
			+ " from li_store s) as s  ${ew.customSqlSegment}</script>")
	IPage<StoreVO> getGoodShopPage(IPage<StoreVO> page, @Param("lon") String lon, @Param("lat") String lat,
			@Param(Constants.WRAPPER) Wrapper<StoreVO> queryWrapper);

	// @Select("select * from li_store ${ew.customSqlSegment}")
	@Select("<script> "
			+ "SELECT * FROM (select *,IFNULL(Floor(6371000 * acos (cos ( radians(#{lat}) ) * cos( radians(lat) ) * cos( radians(lon) - radians(#{lon}) ) "
			+ "   + sin ( radians(#{lat}) ) * sin( radians(lat) ) ) ),0)AS distance , "
			+ "IFNULL((select count(0) FROM li_order o WHERE o.store_id = s.id and o.order_status='COMPLETED' ),0) as monthSales, "
			+ "FORMAT(IFNULL((select SUM(e.store_score)/COUNT(e.store_score) FROM li_store_evaluation e WHERE e.store_id = s.id),5),1) as score "
			+ " from li_store s) as s  ${ew.customSqlSegment}</script>")
	List<StoreVO> getGoodShop(@Param(Constants.WRAPPER) Wrapper<StoreVO> queryWrapper, @Param("lon") String lon,
			@Param("lat") String lat);

	/**
	 * 查询商品VO分页
	 *
	 * @param page
	 *            分页
	 * @param queryWrapper
	 *            查询条件
	 * @return 商品VO分页
	 */
	@Select("select * from li_goods  ${ew.customSqlSegment}")
	IPage<GoodsVO> queryByParamss(IPage<GoodsSearchParams> page,
			@Param(Constants.WRAPPER) Wrapper<GoodsSearchParams> queryWrapper);

	@Select("select IFNULL((select SUM(num) from  li_order_item where goods_id = goods.id and after_sale_status='NOT_APPLIED'),0) as monthSales, FORMAT(IFNULL((select SUM(grade)/COUNT(grade) from  li_member_evaluation where goods_id = goods.id),5),1)  as score ,goods.* FROM li_goods goods LEFT JOIN li_store store on store.id = goods.store_id ${ew.customSqlSegment}")
	IPage<GoodsVO> getByhotGoods(IPage<GoodsAndStores> page,
			@Param(Constants.WRAPPER) Wrapper<GoodsSearchParams> queryWrapper);

	@Select("select IFNULL((select SUM(num) FROM li_order_item WHERE goods_id = goods.id and after_sale_status='NOT_APPLIED'),0) as monthSales,"
			+ " FORMAT(IFNULL((select SUM(grade)/COUNT(grade) FROM li_member_evaluation WHERE goods_id = goods.id),5),1) as score ,"
			+ "goods.id as goodsId,goods.price as price,goods.goods_name as title,goods.original as image FROM li_goods goods LEFT JOIN li_store store on store.id = goods.store_id ${ew.customSqlSegment}")
	IPage<GoodsAndStores> getByNewGoods(IPage<GoodsAndStores> page,
			@Param(Constants.WRAPPER) Wrapper<GoodsAndStores> goodsWrapper);

	@Select("select s.*, IFNULL(Floor(6371000 * acos (    cos ( radians(#{lat}) )    * cos( radians(lat) ) "
			+ "   * cos( radians(lon) - radians(#{lon}) )    + sin ( radians(#{lat}) )    * sin( radians(lat) ) "
			+ "  )  ),0) AS distance,"
			+ "IFNULL((select count(0) FROM li_order o WHERE o.store_id = s.id ),0) as monthSales,"
			+ "FORMAT(IFNULL((select SUM(e.store_score)/COUNT(e.store_score) FROM li_store_evaluation e "
			+ "  WHERE e.store_id = s.id),5),1) as score,"
			+ "store_name as title,store_logo as image from li_store s  ${ew.customSqlSegment}")
	IPage<GoodsAndStores> byNewStores(IPage<GoodsAndStores> page, @Param("lon") String lon, @Param("lat") String lat,
			@Param(Constants.WRAPPER) Wrapper<GoodsAndStores> storeWrapper);

	@Select("<script> SELECT IFNULL((select SUM(num)  FROM li_order_item "
			+ " WHERE goods_id = g.id and after_sale_status='NOT_APPLIED'),0) as monthSales, FORMAT(IFNULL((select SUM(grade)/COUNT(grade) FROM li_member_evaluation "
			+ " WHERE goods_id = g.id),5),1) as score ,g.* FROM li_goods g LEFT JOIN li_store s ON g.store_id = s.id "
			+ "WHERE DATE_ADD( g.create_time, INTERVAL 6 MONTH ) > now() and s.self_operated = 1 "
			+ "<if test=\"cityId!=null and cityId!=''\"> "
			+ " and ((s.store_address_id_path LIKE concat('%',#{cityId},'%') and g.sales_area = 0 ) OR g.store_address_id_path  LIKE concat('%',#{cityId},'%')) "
			+ "</if> <if test=\"goodsName!=null and goodsName!=''\">   "
			+ "    and g.goods_name LIKE concat('%',#{goodsName},'%')   "
			+ "</if>AND s.store_disable = 'OPEN' AND s.delete_flag = false and g.auth_flag = 'PASS' AND g.delete_flag = false AND g.market_enable = 'UPPER'"
			+ "<if test=\"monthSales!=null and monthSales!=''\">   ORDER BY monthSales DESC </if>"
			+ "<if test=\"score!=null and score!=''\">   ORDER BY score DESC </if>"
			+ "<if test=\"sort!=null and sort!=''\">   ORDER BY g.sort DESC </if></script>")
	IPage<GoodsVO> getGoodPage(IPage<GoodsVO> page, @Param("cityId") String cityId,
			@Param("goodsName") String goodsName, @Param("monthSales") Integer monthSales, @Param("score") Double score,
			@Param("sort") String sort);

	@Select("select IFNULL((select SUM(num) FROM li_order_item WHERE goods_id = goods.id and after_sale_status='NOT_APPLIED'),0) as monthSales,"
			+ " FORMAT(IFNULL((select SUM(grade)/COUNT(grade) FROM li_member_evaluation WHERE goods_id = goods.id),5),1) as score,goods.*"
			+ " FROM li_goods goods LEFT JOIN li_store store on store.id = goods.store_id ${ew.customSqlSegment}")
	IPage<GoodsVO> getNewGoods(IPage<GoodsSearchParams> initPage,
			@Param(Constants.WRAPPER) QueryWrapper<GoodsSearchParams> queryWrapper);

	@Select("SELECT DISTINCT(ad_code) adCode  FROM li_region "
			+ " WHERE  id = #{id} OR path LIKE concat('%',#{id},'%')")
	List<String> getListByPath(@Param("id") String id);

	@Select("SELECT  a.createTime AS createTime,  a.sn AS sn,  a.mobile AS mobile, "
			+ " a.nick_name AS realName,  a.location AS location,  ifnull(lr.role_name,'') AS roleName, "
			+ " a.NAME AS gradeName,  RoundDown(a.flow_price,2) AS flowPrice, "
			+ " RoundDown(a.commission,2) AS commission,  a.transaction_type AS transactionType, "
			+ " a.typeName AS typeName,  RoundDown(sum( a.tsMoney ),2) AS tsMoney, "
			+ " RoundDown(sum( a.qyMoney ),2) AS qyMoney,  RoundDown(sum( a.syMoney ),2) AS syMoney, "
			+ " RoundDown(sum( a.adminMoney ),2) AS adminMoney, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsRealName, '' ) SEPARATOR '' ) AS tsRealName, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyRealName, '' ) SEPARATOR '' ) AS qyRealName, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.syRealName, '' ) SEPARATOR '' ) AS syRealName, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsMobile, '' ) SEPARATOR '' ) AS tsMobile, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyMobile, '' ) SEPARATOR '' ) AS qyMobile, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.syMobile, '' ) SEPARATOR '' ) AS syMobile, "
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsStoreName, '' ) SEPARATOR '' ) AS tsStoreName  FROM  ( "
			+ " SELECT  lo.create_time AS createTime,   lo.sn,   lm.id,   lm.mobile, "
			+ "  lm.nick_name,   lm.location,   lmc.NAME,   lo.goods_price AS flow_price, "
			+ "  lo.commission,   lwd.transaction_type,   '订单费' AS typeName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS_REFUND', - abs( lwd.money ), 0 ) +IF( lwd.transaction_type = 'WALLET_COMMISSION_TS', lwd.money, 0 ) tsMoney, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY_REFUND', - abs( lwd.money ), 0 ) +IF( lwd.transaction_type = 'WALLET_COMMISSION_SY', lwd.money, 0 ) syMoney, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ),- abs( lwd.money ), 0 ) + IF ( lwd.transaction_type IN ( 'WALLET_COMMISSION_FW', 'WALLET_COMMISSION_TS_REFUND', 'WALLET_COMMISSION_SY_REFUND' ) AND lwd.payee <> 'admin' , lwd.money, 0 ) qyMoney, "
			+ " IF ( lwd.payer = 'admin', - abs( lwd.money ), 0 ) + IF ( lwd.payee = 'admin', lwd.money, 0 ) AS adminMoney, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', lm1.nick_name, NULL ) tsRealName, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type NOT LIKE '%REFUND' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ), lm2"
			+ ".nick_name,IF ( lwd.transaction_type = 'WALLET_COMMISSION_FW' AND lwd.payee <> 'admin', lm1.nick_name, NULL ) ) qyRealName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY', lm1.nick_name, NULL ) syRealName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', ls.store_name, NULL ) tsStoreName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', lm1.mobile, NULL ) tsMobile, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type NOT LIKE '%_REFUND' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ), lm2"
			+ ".mobile, IF ( lwd.transaction_type = 'WALLET_COMMISSION_FW' AND lwd.payee <> 'admin', lm1.mobile, NULL ) ) qyMobile, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY', lm1.mobile, NULL ) syMobile  FROM "
			+ "  li_order lo   LEFT JOIN li_member lm ON lo.member_id = lm.id "
			+ "  LEFT JOIN li_grade_level lgl ON lgl.member_id = lm.id and lgl.delete_flag = 0 "
			+ "  LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id "
			+ "  LEFT JOIN li_wallet_detail lwd ON lo.sn = lwd.sn    AND lwd.transaction_type <> \"WALLET_PAY\" "
			+ "  LEFT JOIN li_member lm1 ON lwd.payee = lm1.id   LEFT JOIN li_member lm2 ON lwd.payer = lm2.id "
			+ "  LEFT JOIN li_store ls ON lm1.store_id = ls.id   WHERE   lo.pay_status = \"PAID\"  "
			+ "  AND lo.order_status = \"COMPLETED\" AND lm.id is not null  UNION  SELECT "
			+ "  lt.create_time AS createTime,   lt.sn,   lm.id,   lm.mobile,   lm.nick_name, "
			+ "  lm.location,   lmc.NAME,   lt.transfer_money AS flow_price,   lt.commission, "
			+ "  lwd.transaction_type,   '转账费' AS typeName,   0 tsMoney,   0 syMoney, "
			+ " IF (lwd.payer <> lm.id AND lwd.payer <> \"admin\"AND lwd.payee <> \"admin\" ,- abs( lwd.money ),0 ) + IF( lwd.transaction_type = \"WALLET_COMMISSION_FW\" AND lwd.payee <> \"admin\" , lwd.money, 0 ) qyMoney, "
			+ " IF( lwd.payer = \"admin\", - abs( lwd.money ), 0 ) +IF( lwd.payee = \"admin\", lwd.money, 0 ) AS adminMoney, "
			+ "  '' tsRealName,  IF "
			+ "  ( lwd.transaction_type = \"WALLET_COMMISSION_FW\", lm1.nick_name, NULL ) qyRealName, "
			+ "  '' syRealName,   '' tsStoreName,   '' tsMobile,  IF "
			+ "  ( lwd.transaction_type = \"WALLET_COMMISSION_FW\", lm1.mobile, NULL ) qyMobile,   '' syMobile  "
			+ " FROM   li_transfer lt   LEFT JOIN li_member lm ON lt.payer = lm.id "
			+ "  LEFT JOIN li_grade_level lgl ON lgl.member_id = lm.id and lgl.delete_flag = 0 "
			+ "  LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id "
			+ "  LEFT JOIN li_wallet_detail lwd ON lt.sn = lwd.sn    AND lwd.transaction_type <> \"WALLET_PAY\" "
			+ "  LEFT JOIN li_member lm1 ON lwd.payee = lm1.id   LEFT JOIN li_store ls ON lm1.store_id = ls.id  "
			+ " WHERE  lt.state = \"success\"  AND (lt.type <>  'PORT_FEE' or  isnull(lt.type)) "
			+ " AND lm.id is not null  ) a "
			+ " LEFT JOIN li_partner lp on lp.member_id = a.id AND lp.partner_state = \"0\" AND lp.delete_flag = \"0\" and lp.audit_state = 'PASS' "
			+ " LEFT JOIN li_role lr on lr.id = lp.role_id ${ew.customSqlSegment}")
	IPage<CommissionDistributionSale> GetCommissionDistributionSale(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

	@Select(" SELECT a.storeId, a.tsStoreId, a.createTime AS createTime, a.sn AS sn,"
			+ " a.mobile AS mobile, a.nick_name AS realName, a.location AS location,"
			+ " ifnull( lr.role_name, '' ) AS roleName, a.NAME AS gradeName,"
			+ " ifnull( Round( a.flow_price, 2 ), 0 ) AS flowPrice,"
			+ " ifnull( Round( a.commission, 2 ), 0 ) AS commission, a.transaction_type AS transactionType,"
			+ " a.typeName AS typeName, ifnull( Round( sum( a.tsMoney ), 2 ), 0 ) AS tsMoney,"
			+ " ifnull( Round( sum( a.qyMoney ), 2 ), 0 ) AS qyMoney,"
			+ " ifnull( Round( sum( a.syMoney ), 2 ), 0 ) AS syMoney,"
			+ " ifnull( Round( sum( a.adminMoney ), 2 ), 0 ) AS adminMoney,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsRealName, '' ) SEPARATOR '' ) AS tsRealName,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyRealName, '' ) SEPARATOR '' ) AS qyRealName,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.syRealName, '' ) SEPARATOR '' ) AS syRealName,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsMobile, '' ) SEPARATOR '' ) AS tsMobile,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyMobile, '' ) SEPARATOR '' ) AS qyMobile,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.syMobile, '' ) SEPARATOR '' ) AS syMobile,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsStoreName, '' ) SEPARATOR '' ) AS tsStoreName  FROM"
			+ " (SELECT lo.store_id as storeId, lm1.store_id as tsStoreId, lo.create_time AS createTime,"
			+ " lo.sn, lm.id, lm.mobile, lm.nick_name, lm.location, lmc.NAME,"
			+ " lo.goods_price AS flow_price, lo.commission, lwd.transaction_type, '订单费' AS typeName,"
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS_REFUND', - abs( lwd.money ), 0 ) +IF( lwd.transaction_type = 'WALLET_COMMISSION_TS', lwd.money, 0 ) tsMoney, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY_REFUND', - abs( lwd.money ), 0 ) +IF( lwd.transaction_type = 'WALLET_COMMISSION_SY', lwd.money, 0 ) syMoney, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ),- abs( lwd.money ), 0 ) + IF ( lwd.transaction_type IN ( 'WALLET_COMMISSION_FW', 'WALLET_COMMISSION_TS_REFUND', 'WALLET_COMMISSION_SY_REFUND' ) AND lwd.payee <> 'admin' , lwd.money, 0 ) qyMoney, "
			+ " IF ( lwd.payer = 'admin', - abs( lwd.money ), 0 ) + IF ( lwd.payee = 'admin', lwd.money, 0 ) AS adminMoney, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', lm1.nick_name, NULL ) tsRealName, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type NOT LIKE '%REFUND' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ), lm2"
			+ ".nick_name,IF ( lwd.transaction_type = 'WALLET_COMMISSION_FW' AND lwd.payee <> 'admin', lm1.nick_name, NULL ) ) qyRealName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY', lm1.nick_name, NULL ) syRealName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', ls.store_name, NULL ) tsStoreName, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_TS', lm1.mobile, NULL ) tsMobile, "
			+ " IF ( lwd.payer <> 'admin' AND lwd.payee <> 'admin' AND lwd.transaction_type NOT LIKE '%_REFUND' AND lwd.transaction_type IN ( 'WALLET_COMMISSION_TS', 'WALLET_COMMISSION_SY' ), lm2"
			+ ".mobile, IF ( lwd.transaction_type = 'WALLET_COMMISSION_FW' AND lwd.payee <> 'admin', lm1.mobile, NULL ) ) qyMobile, "
			+ " IF ( lwd.transaction_type = 'WALLET_COMMISSION_SY', lm1.mobile, NULL ) syMobile FROM li_order lo"
			+ " LEFT JOIN li_member lm ON lo.member_id = lm.id"
			+ " LEFT JOIN li_grade_level lgl ON lgl.member_id = lm.id and lgl.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id"
			+ " LEFT JOIN li_wallet_detail lwd ON lo.sn = lwd.sn  AND lwd.transaction_type <> 'WALLET_PAY'"
			+ " LEFT JOIN li_member lm1 ON lwd.payee = lm1.id  LEFT JOIN li_member lm2 ON lwd.payer = lm2.id "
			+ " LEFT JOIN li_store ls ON lm1.store_id = ls.id  WHERE lo.pay_status = 'PAID' "
			+ " AND lo.order_status = 'COMPLETED'  AND lm.id is not null  ) a"
			+ " LEFT JOIN li_partner lp ON lp.member_id = a.id  AND lp.partner_state = '0'   AND lp.delete_flag = '0'"
			+ " LEFT JOIN li_role lr ON lr.id = lp.role_id ${ew.customSqlSegment}")
	IPage<CommissionDistributionSale> GetCommissionDistribution(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

	@Select("SELECT a.createTime AS createTime, a.sn AS sn, a.mobile AS mobile,"
			+ " a.nick_name AS realName, a.location AS location, lr1.role_name AS roleName,"
			+ " a.NAME AS gradeName, RoundDown(a.recharge_money,2) AS flowPrice, a.remark AS typeName,"
			+ " RoundDown(SUM(adminMoney),2) AS adminMoney, RoundDown(SUM(tsMoney),2) AS tsMoney,"
			+ " RoundDown(SUM(qyMoney),2) AS qyMoney,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsRealName, '' ) SEPARATOR '' ) AS tsRealName,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyRealName, '' ) SEPARATOR '' ) AS qyRealName,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsMobile, '' ) SEPARATOR '' ) AS tsMobile,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.qyMobile, '' ) SEPARATOR '' ) AS qyMobile,"
			+ " GROUP_CONCAT( DISTINCT IFNULL( a.tsStoreName, '' ) SEPARATOR '' ) AS tsStoreName FROM ("
			+ " SELECT lr.create_time AS createTime,  lr.recharge_sn AS sn, lm.id, lm.mobile,"
			+ " lm.nick_name, lmc.NAME, lm.location, lr.recharge_money,"
			+ " IF( lwd.payee_owner = \"PROMOTE\", lwd.money, 0 ) tsMoney, "
			+ " IF( lwd.payer <> lr.member_id AND lwd.payer <> 'admin' AND lwd.payee <> 'admin',- abs( lwd.money ) , 0 ) + "
			+ " IF( lwd.payee_owner = \"PROMOTE_HY\" AND lwd.payee <> 'admin', lwd.money, 0 ) qyMoney, "
			+ " IF( lwd.payer = 'admin', - abs( lwd.money ), 0 ) + IF( lwd.payee = 'admin', lwd.money, 0 ) AS adminMoney, "
			+ " IF ( lwd.payee_owner = \"PROMOTE\", lm1.real_name, NULL ) tsRealName,"
			+ " IF ( lwd.payee_owner = \"PROMOTE_HY\" AND payee <> \"admin\", lm1.nick_name, NULL ) qyRealName,"
			+ " IF ( lwd.payee_owner = \"PROMOTE\", lm1.mobile, NULL ) tsMobile,"
			+ " IF ( lwd.payee_owner = \"PROMOTE_HY\" AND payee <> \"admin\", lm1.mobile, NULL ) qyMobile,"
			+ " IF ( lwd.payee_owner = \"PROMOTE\", ls.store_name, NULL ) tsStoreName,  lwd.remark FROM"
			+ " li_recharge lr LEFT JOIN li_member lm ON lr.member_id = lm.id"
			+ " LEFT JOIN li_grade_level lgl ON lgl.member_id = lm.id and lgl.delete_flag = 0 "
			+ " LEFT JOIN li_membership_card lmc ON lmc.id = lgl.grade_id"
			+ " LEFT JOIN li_wallet_detail lwd ON lr.recharge_sn = lwd.sn  AND lwd.transaction_type <> \"WALLET_PAY\""
			+ " LEFT JOIN li_member lm1 ON lwd.payee = lm1.id LEFT JOIN li_store ls ON lm1.store_id = ls.id "
			+ " WHERE  lr.recharge_type = '3'  AND lr.pay_status ='PAID' AND lm.id is not null  ) a"
			+ " LEFT JOIN li_partner lp ON lp.member_id = a.id AND lp.partner_state = \"0\" AND lp.delete_flag = \"0\" "
			+ " LEFT JOIN li_role lr1 ON lr1.id = lp.role_id  ${ew.customSqlSegment}")
	IPage<CommissionDistributionSale> GetCommissionDistributionMember(Page<Object> initPage,
			@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

}
